package com.affablebeans.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.affablebeans.model.entity.Category;
import com.affablebeans.model.entity.Customer;
import com.affablebeans.model.entity.Order;
import com.affablebeans.model.entity.OrderedProduct;
import com.affablebeans.model.entity.Product;

/**
 * 
 * @author Bora SAYINER
 * @since Jan 26, 2015 10:39:14 PM
 * @version 1.0.0
 */
public class OrderedProductDAL extends AbstractDAL<OrderedProduct> {

	private static final String INSERT_SQL = "INSERT INTO ordered_product VALUES (?,?,?)";
	private static final String UPDATE_SQL = "UPDATE ordered_product SET quantity=? WHERE (customer_order_id = ? AND product_id=?)";
	private static final String DELETE_SQL = "DELETE FROM ordered_product WHERE (customer_order_id = ? AND product_id=?)";
	private static final String FIND_BY_ID_SQL = "SELECT op.quantity,co.id AS customer_order_id,co.amount,co.date_created,co.confirmation_number,c.id AS customer_id,c.name,c.email,c.phone,c.address,c.city_region,c.cc_number,p.id AS product_id,p.name AS product_name,p.price,p.description,p.last_update,cat.id AS category_id,cat.name AS category_name FROM ordered_product AS op INNER JOIN customer_order AS co ON co.id=op.customer_order_id INNER JOIN product AS p ON p.id=op.product_id INNER JOIN customer AS c ON c.id=co.customer_id INNER JOIN category AS cat ON cat.id=p.category_id WHERE (co.id=? AND p.id=?)";
	private static final String GET_ALL_SQL = "SELECT op.quantity,co.id AS customer_order_id,co.amount,co.date_created,co.confirmation_number,c.id AS customer_id,c.name,c.email,c.phone,c.address,c.city_region,c.cc_number,p.id AS product_id,p.name AS product_name,p.price,p.description,p.last_update,cat.id AS category_id,cat.name AS category_name FROM ordered_product AS op INNER JOIN customer_order AS co ON co.id=op.customer_order_id INNER JOIN product AS p ON p.id=op.product_id INNER JOIN customer AS c ON c.id=co.customer_id INNER JOIN category AS cat ON cat.id=p.category_id";

	public OrderedProductDAL(DataSource dataSource) {
		super(dataSource, "ordered_product");
	}

	@Override
	public boolean insert(OrderedProduct entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(INSERT_SQL)) {
				statement.setInt(1, entity.getOrder().getId());
				statement.setInt(2, entity.getProduct().getId());
				statement.setInt(3, entity.getQuantity());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean update(OrderedProduct entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(UPDATE_SQL)) {
				statement.setInt(1, entity.getQuantity());
				statement.setInt(2, entity.getOrder().getId());
				statement.setInt(3, entity.getProduct().getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean delete(OrderedProduct entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(DELETE_SQL)) {
				statement.setInt(1, entity.getOrder().getId());
				statement.setInt(2, entity.getProduct().getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public OrderedProduct findById(int... id) throws SQLException {
		OrderedProduct orderedProduct = null;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(FIND_BY_ID_SQL)) {
				statement.setInt(1, id[0]);
				statement.setInt(2, id[1]);
				ResultSet resultSet = statement.executeQuery();
				if (resultSet.next()) {
					orderedProduct = createEntity(resultSet);
				}
			}
		}
		return orderedProduct;
	}

	@Override
	public List<OrderedProduct> getAll() throws SQLException {
		List<OrderedProduct> orderedProducts = new ArrayList<OrderedProduct>();
		try (Connection connection = createConnection()) {
			try (Statement statement = connection.createStatement()) {
				ResultSet resultSet = statement.executeQuery(GET_ALL_SQL);
				while (resultSet.next()) {
					orderedProducts.add(createEntity(resultSet));
				}
			}
		}
		return orderedProducts;
	}

	@Override
	OrderedProduct createEntity(ResultSet resultSet) throws SQLException {
		OrderedProduct orderedProduct = new OrderedProduct();
		orderedProduct.setQuantity(resultSet.getInt("quantity"));
		{
			Order order = new Order();
			order.setId(resultSet.getInt("customer_order_id"));
			order.setAmount(resultSet.getDouble("amount"));
			order.setCreatedDate(resultSet.getDate("date_created"));
			order.setConfirmationNumber(resultSet.getInt("confirmation_number"));
			{
				Customer customer = new Customer();
				customer.setId(resultSet.getInt("customer_id"));
				customer.setName(resultSet.getString("name"));
				customer.setEmail(resultSet.getString("email"));
				customer.setPhone(resultSet.getString("phone"));
				customer.setAddress(resultSet.getString("address"));
				customer.setCityRegion(resultSet.getString("city_region"));
				customer.setCreditCartNumber(resultSet.getString("cc_number"));
				order.setCustomer(customer);
			}
			orderedProduct.setOrder(order);
			Product product = new Product();
			product.setId(resultSet.getInt("product_id"));
			product.setName(resultSet.getString("product_name"));
			product.setPrice(resultSet.getDouble("price"));
			product.setDescription(resultSet.getString("description"));
			product.setLastUpdate(resultSet.getDate("last_update"));
			{
				Category category = new Category();
				category.setId(resultSet.getInt("category_id"));
				category.setName(resultSet.getString("category_name"));
				product.setCategory(category);
			}
			orderedProduct.setProduct(product);
		}
		return orderedProduct;
	}

}
